package com.sq.partner.manager.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.sq.partner.manager.common.Config;
import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.common.DictCache;
import com.sq.partner.manager.common.Result;
import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.ActirationCardActivatedDao;
import com.sq.partner.manager.dao.ActirationCardBatchInsuredDao;
import com.sq.partner.manager.dao.CardInfoDao;
import com.sq.partner.manager.dao.CommamtFeeDao;
import com.sq.partner.manager.dao.DictOccupDao;
import com.sq.partner.manager.dao.FacadeAppApplicantDao;
import com.sq.partner.manager.dao.FacadeAppBeneficiaryDao;
import com.sq.partner.manager.dao.FacadeAppFormDao;
import com.sq.partner.manager.dao.FacadeAppFormExtendDao;
import com.sq.partner.manager.dao.FacadeAppInsuredDao;
import com.sq.partner.manager.dao.FacadeNewPaymentDao;
import com.sq.partner.manager.dao.FacadePaymentDao;
import com.sq.partner.manager.dao.FacadeTempPolicyDao;
import com.sq.partner.manager.mybatis.model.ActirationCardActivated;
import com.sq.partner.manager.mybatis.model.ActirationCardBatchInsuredDto;
import com.sq.partner.manager.mybatis.model.CardInfo;
import com.sq.partner.manager.mybatis.model.CommamtFee;
import com.sq.partner.manager.mybatis.model.DictDto;
import com.sq.partner.manager.mybatis.model.DictOccupDto;
import com.sq.partner.manager.mybatis.model.FacadeAppApplicant;
import com.sq.partner.manager.mybatis.model.FacadeAppForm;
import com.sq.partner.manager.mybatis.model.FacadeAppFormExtend;
import com.sq.partner.manager.mybatis.model.FacadeAppInsured;
import com.sq.partner.manager.mybatis.model.FacadeHis;
import com.sq.partner.manager.mybatis.model.FacadeNewPayment;
import com.sq.partner.manager.mybatis.model.FacadePayment;
import com.sq.partner.manager.mybatis.model.FacadeTempPolicy;
import com.sq.partner.manager.mybatis.model.FacadeTransLog;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.service.ActirationCardActivatedService;
import com.sq.partner.manager.service.FacadeAppApplicantService;
import com.sq.partner.manager.service.FacadeAppBeneficiaryService;
import com.sq.partner.manager.service.FacadeAppFormService;
import com.sq.partner.manager.service.FacadeAppInsuredService;
import com.sq.partner.manager.service.FacadeHisService;
import com.sq.partner.manager.service.FacadeTempPolicyService;
import com.sq.partner.manager.service.PayService;
import com.sq.partner.manager.util.DateUtil;
import com.sq.partner.manager.util.ExcelUtil;
import com.sq.partner.manager.util.FreeMarkerUtil;
import com.sq.partner.manager.util.HttpConnectorUtil;
import com.sq.partner.manager.util.IdCardUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.StrUtil;

/**
 * 激活卡激活Service
 * 
 * @author zhouxiuxiu
 *
 */
@Service
public class ActirationCardActivatedServiceImpl implements
        ActirationCardActivatedService {
    private static Logger logger = LogUtil
            .getLog(ActirationCardActivatedServiceImpl.class);

    @Resource
    private ActirationCardActivatedDao actirationCardActivatedDao;
    @Resource
    private CardInfoDao cardInfoDao;
    @Resource
    private FacadeAppInsuredDao facadeAppInsuredDao;
    @Resource
    private FacadeAppFormDao facadeAppFormDao;
    @Resource
    private FacadeTempPolicyDao facadeTempPolicyDao;
    @Resource
    private FacadeAppApplicantDao facadeAppApplicantDao;
    @Resource
    private FacadeAppBeneficiaryDao facadeAppBeneficiaryDao;
    @Resource
    private FacadeAppFormExtendDao facadeAppFormExtendDao;
    @Resource
    private FacadeTempPolicyService facadeTempPolicyService;
    @Resource
    private FacadeAppFormService facadeAppFormService;
    @Resource
    private FacadeAppInsuredService facadeAppInsuredService;
    @Resource
    private FacadeAppBeneficiaryService facadeAppBeneficiaryService;
    @Resource
    private FacadeAppApplicantService facadeAppApplicantService;
    @Resource
    private FacadePaymentDao facadePayDao;
    @Resource
    private PayService payService;
    @Resource
    private FacadeHisService facadeHisService;
    @Resource
    private DictOccupDao dictOccupDao;
    @Resource
    private ActirationCardBatchInsuredDao actirationCardBatchInsuredDao;
    @Resource
    private CommamtFeeDao commamtFeeDao;//查询isNewPay字段
    @Resource
    private FacadeNewPaymentDao facadenewPayDao;//对接新支付平台

    /**
     * 验证卡号和密码是否正确
     * 
     * @param cardNo
     * @param password
     * 
     * @exception
     */
    @Override
    public Result checkCard(String cardNo, String password) throws Exception {
        // 验证卡号密码是否正确
        CardInfo card = cardInfoDao.getCardInfoByCardNo(cardNo);
        if (card == null) {
            return Result.error("卡号不正确！");
        }
        if (!password.equals(card.getCardPw())) {
            return Result.error("密码不正确！");
        }
        if (!Constants.PROD_TYPE_NLIFE.equals(card.getTypeCode())) {
            return Result.error("激活卡系统目前只支持新意健险激活卡激活！");
        }
        if (!Constants.ACTIR_CARD_ACTIVABLE.equals(card.getStatus())
                && !Constants.ACTIR_CARD_ACTIVABLE_USED.equals(card.getStatus())) {
            return Result.error("此卡不是可激活或已激活状态，不可登录！");
        }
        if (StrUtil.isEmpty(card.getPlanNo())) {
            return Result.error("此卡没有关联方案，请联系管理员！");
        }
        return Result.success(card);
    }

    /**
     * 取得方案和产品信息
     * 
     * @param planNo
     * @return
     * @throws Exception
     */
    @Override
    public ActirationCardActivated getProdInfoByPlanNo(String planNo)
            throws Exception {
        // 取得产品信息
        ActirationCardActivated prodInfo = actirationCardActivatedDao
                .getProdInfoByPlanNo(planNo);

        return prodInfo;
    }

    /**
     * 保存个人投保信息
     * 
     * @param facadeAppForm
     * @param extend
     * @param facadeAppApplicant
     * @param facadeAppInsured
     * @param gatewayuser
     * @param benefList
     * @param cardNo
     * @return
     * @throws Exception
     */
    @Override
    public Result saveOrUpdateSingleTemporary(FacadeAppForm facadeAppForm,
            FacadeAppFormExtend extend, FacadeAppApplicant facadeAppApplicant,
            List<FacadeAppInsured> facadeAppInsureds, GatewayUser gatewayuser,
            String cardNo, String isSeeMoney) throws Exception {
        CardInfo card = cardInfoDao.getCardInfoByCardNo(cardNo);
        if (null == card) {
            return Result.error("激活卡不存在，请联系管理员！");
        }
        if (!Constants.ACTIR_CARD_ACTIVABLE.equals(card.getStatus())) {
            return Result.error("激活卡不是可激活状态，不可激活！");
        }
        
        facadeAppForm.setDeptNo(card.getDeptNo());
        facadeAppForm.setInputEmp(card.getOperatorNo());
        FacadeTempPolicy facadeTempPolicy = new FacadeTempPolicy();
        // 查询合作伙伴和所属机构 PARTNER_NO DPT_CDE
        facadeTempPolicy.setPartnerNo(facadeAppForm.getPartnerNo());
        facadeTempPolicy.setPartnerSaleNo(facadeAppForm.getPartneSaleNo());
        facadeTempPolicy.setIsSeeMoney(isSeeMoney);
        if (StrUtil.isEmpty(facadeAppForm.getStagNo())) {
            facadeTempPolicy.setStatus(Constants.POL_STATUS_01);
            facadeTempPolicy.setOperator(gatewayuser.getUserCode());
            facadeTempPolicy.setInputFate(new Date());
            facadeTempPolicy.setProdType(Constants.PROD_TYPE_NLIFE);
            // 添加暂存单
            facadeTempPolicy = facadeTempPolicyService
                    .saveFacadeTempPolicy(facadeTempPolicy);
            logger.info("暂存表插入成功");

            facadeAppForm.setStagNo(facadeTempPolicy.getStagNo());
            logger.info("暂存单:" + facadeTempPolicy.getStagNo() + "生成");
            // 添加投保单
            facadeAppForm = facadeAppFormService
                    .saveFacadeAppForm(facadeAppForm);
            logger.info("保单表插入成功");
            // 得到保单号
            String appNo = facadeAppForm.getAppNo();
            // 保存投保单扩展信息
            extend.setAppNo(appNo);
            facadeAppFormExtendDao.saveFacadeAppFormExtend(extend);
            logger.info("保单扩展表插入成功");

            facadeAppApplicant.setAppNo(appNo);
            logger.info("投保单:" + appNo + "生成");
            // 添加被保人
            for (FacadeAppInsured facadeAppInsured:facadeAppInsureds) {
                facadeAppInsured.setAppNo(appNo);
                facadeAppInsuredService.saveFacadeAppInsured(facadeAppInsured);
            }
            logger.info("被保人表插入成功");
            // 添加投保人
            facadeAppApplicant = facadeAppApplicantService
                    .saveFacadeAppApplicant(facadeAppApplicant);
            logger.info("投保人表插入成功");
//            // 生成受益人标号
//            for (FacadeAppBeneficiary facadeAppBeneficiary : benefList) {
//                facadeAppBeneficiary.setAppNo(appNo);
//                facadeAppBeneficiary.setInsuredClientNo(insuredClientNo);
//                facadeAppBeneficiaryService
//                        .saveFacadeAppBeneficiary(facadeAppBeneficiary);
//            }
//
//            logger.info("受益人表插入成功");
            String stagNoAndappNo = facadeTempPolicy.getStagNo() + "," + appNo;
//                    + "," + insuredClientNo;
            return Result.success("暂存成功", stagNoAndappNo);
        } else {
            facadeTempPolicy.setStagNo(facadeAppForm.getStagNo());
            facadeTempPolicy.setUpdateTime(DateUtil.getNowTimestamp());
            facadeTempPolicyDao.updateTempPolicyNotNull(facadeTempPolicy);
            facadeAppFormDao.updateFacadeAppFormNotNull(facadeAppForm);
            facadeAppFormExtendDao.updateFacadeAppFormExtend(extend);
            //先删除所有被保险人，再插入
            facadeAppInsuredService.deleteInsuredByAppNo(facadeAppForm.getAppNo());
            for (FacadeAppInsured facadeAppInsured:facadeAppInsureds) {
                facadeAppInsuredService.saveFacadeAppInsured(facadeAppInsured);
            }
            
            facadeAppApplicantDao
                    .updateFacadeAppApplicantNotNull(facadeAppApplicant);
//            // 先删除所有受益人相关信息,找出被保险人编号,然后生成受益人标号插入数据
//            facadeAppBeneficiaryService
//                    .delFacadeAppBeneficiaryByAppNo(facadeAppForm.getAppNo());
//            for (FacadeAppBeneficiary facadeAppBeneficiary : benefList) {
//                facadeAppBeneficiary.setAppNo(facadeAppForm.getAppNo());
//                facadeAppBeneficiary.setInsuredClientNo(facadeAppInsured
//                        .getClientNo());
//                facadeAppBeneficiaryService
//                        .saveFacadeAppBeneficiary(facadeAppBeneficiary);
//            }
            return Result.success("暂存成功");
        }
    }

    /**
     * 根据暂存单查询保单表,投保信息表,被表信息表
     * 
     * @param stagNo
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> queryAllObjectMessage(String stagNo)
            throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        // 暂存单查询投保单信息
        FacadeAppForm facadeAppForm = facadeAppFormDao
                .queryNLifeSingleFacadeAppFormByStagNo(stagNo);
        // String appType = (String)
        // facadeAppFormDao.query("queryAppTypeByPolno",
        // stagNo);
        String polSubDate = "";
        Timestamp subDate = facadeAppForm.getPolSubDate();
        if (subDate != null) {
            polSubDate = subDate.toString().substring(0, 10);
        }
        // 投保单表时间处理
        Map<String, String> ms = new HashMap<String, String>();
        ms.put("appNo", facadeAppForm.getAppNo());

        // 取得投保扩展信息
        FacadeAppFormExtend extend = facadeAppFormExtendDao
                .queryFacadeAppFormExtend(facadeAppForm.getAppNo());
        // 取得投保人信息
        FacadeAppApplicant facadeAppApplicant = (FacadeAppApplicant) facadeAppApplicantDao
                .selectFacadeAppApplicantByMap(ms);
        // 取得被保人信息
        List<FacadeAppInsured> facadeAppInsured = (List<FacadeAppInsured>) facadeAppInsuredDao
                .selectFacadeAppInsuredList(ms);
//        // 取得受益人信息
//        List<FacadeAppBeneficiary> facadeAppBeneficiarys = (List<FacadeAppBeneficiary>) facadeAppBeneficiaryDao
//                .selectFacadeAppBeneficiaryByMap(ms);
        // 取得暂存单信息
        FacadeTempPolicy facadeTempPolicy = facadeTempPolicyDao
                .queryFacadeTempPolicyById(stagNo);
        map.put("facadeAppForm", facadeAppForm);
        // map.put("appType", appType);
        map.put("facadeAppFormExtend", extend);
        map.put("facadeAppApplicant", facadeAppApplicant);
        map.put("facadeAppInsured", facadeAppInsured);
//        map.put("facadeAppBeneficiarys", facadeAppBeneficiarys);
        map.put("polSubDate", polSubDate);
        map.put("facadeTempPolicy", facadeTempPolicy);
        return map;
    }

    /**
     * 处理状态为01,11,13 暂存单 根据暂存单状态判断保单是否投保成功 报文返回错误信息errInfo,resultCode;
     * 
     * @param facadeTempPolicy
     * @return
     * @throws Exception
     */
    @Override
    public FacadeTempPolicy sendNlife(CardInfo cardInfo,
            FacadeTempPolicy facadeTempPolicy) throws Exception {
        boolean flag = true;

        String statusCode = "";// 成功返回状态
        String trialUrl = "";
        String trialTemplates = "";
        String policyType = facadeTempPolicy.getProdType();
        String transCode = "";
        if (StrUtil.isNotEmpty(policyType)) {
            if (Constants.PROD_TYPE_NLIFE.equals(policyType)) { // 新意健险
                trialUrl = Constants.NLIFE_TRIAL_URL;
                trialTemplates = Constants.NLIFE_T01_TEMPLATES;
                transCode = Constants.GW_CH_TX_T01;
                statusCode = Constants.NLIFE_STATUS_CODE;
            }
        }

        logger.info("暂存单:[" + facadeTempPolicy.getStagNo() + "]请求开始");
        FacadeAppForm sfacadeAppForm = new FacadeAppForm();
        sfacadeAppForm.setStagNo(facadeTempPolicy.getStagNo());
        // 取录入，待支付，和支付失败的保单
        sfacadeAppForm.setStatus("01,11,13");
        // 根据暂存单号拿到保单所有数据
        List<FacadeAppForm> facadeAppForms = facadeAppFormDao
                .selectFacadeAppFormList(sfacadeAppForm);
        for (int i = 0; i < facadeAppForms.size(); i++) {
            Map<String, String> backmessage = null;
            FacadeTransLog facadeTransLog = null;
            // 生成发送报文
            String responseXml = "";
            FacadeAppForm facadeAppForm = facadeAppForms.get(i);
            facadeAppForm.setStringEffDate(DateUtil.format(
                    facadeAppForm.getEffDate(), "yyyy-MM-dd")
                    + " "
                    + (facadeAppForm.getEffHours() == null ? "00"
                            : facadeAppForm.getEffHours() + ":00:00"));
            facadeAppForm.setStringTermDate(DateUtil.format(
                    facadeAppForm.getTermDate(), "yyyy-MM-dd")
                    + " "
                    + (facadeAppForm.getTermHours() == null ? "00"
                            : facadeAppForm.getTermHours() + ":00:00"));
            facadeAppForm.setUrlApp_No(facadeAppForm.getAppNo());
            String appFormstatus = facadeAppForm.getStatus();
            // 如果投保单的状态为非01 就是待支付状态
            if (StrUtil.isNotEmpty(appFormstatus)
                    && !appFormstatus.equals(Constants.POL_STATUS_01)) {
                facadeAppForm.setResultCode(statusCode);
                facadeAppForms.set(i, facadeAppForm);
                continue;
            }
            try {
                logger.info("投保单:[" + facadeAppForm.getAppNo() + "]");
                facadeTransLog = new FacadeTransLog();

                // 生成交易流水id
                String transNo = payService
                        .generateTradingSerialNumber(facadeAppForm.getAppNo());
                // 装配投保单数据
                Map<String, Object> map = createFacadeAppForm(facadeAppForm);
//                List<FacadeAppInsured> FacadeAppInsureds = (List<FacadeAppInsured>) map
//                        .get("facadeAppInsureds");
//                for (int m = 0; m < FacadeAppInsureds.size(); m++) {
//                    List<FacadeAppBeneficiary> facadeAppBeneficiarys = FacadeAppInsureds
//                            .get(m).getFacadeAppBeneficiarys();
//                    if (facadeAppBeneficiarys != null) {
//                        for (int n = 0; n < facadeAppBeneficiarys.size(); n++) {
//                            facadeAppBeneficiarys
//                                    .get(n)
//                                    .setBenePct(
//                                            String.valueOf((int) (Float
//                                                    .parseFloat(facadeAppBeneficiarys
//                                                            .get(n)
//                                                            .getBenePct()) * 100)));
//                        }
//                    }
//                }
                map.put("SERIALNUMBER", transNo);
                map.put("USERCODE", facadeTempPolicy.getOperator());

                map.put("CURRENCY", "CNY");
                map.put("BENIFITPRINTFLAG", "1");
                FacadeAppFormExtend extend = facadeAppFormExtendDao
                        .queryFacadeAppFormExtend(facadeAppForm
                                .getAppNo());
                
                map.put("facadeAppFormExtend",extend);
                map.put("cardNo", cardInfo.getCardNo());
                map.put("isSeeMoney", facadeTempPolicy.getIsSeeMoney());
                String requestXml = FreeMarkerUtil.process(
                        Config.getProp(trialTemplates), map);
                logger.info("请求报文:" + requestXml);
                // 发送请求时插入交易表
                facadeTransLog.setTransNo(transNo);
                facadeTransLog.setTransCode(transCode);
                facadeTransLog.setStagNo(facadeTempPolicy.getStagNo());
                facadeTransLog.setAppNo(facadeAppForm.getAppNo());
                // 添加交易表
                facadeTransLog.setRequestXml(requestXml);
                facadeTransLog.setRequestDate(new Date());
                responseXml = HttpConnectorUtil.interaction(
                        Config.getProp(trialUrl), requestXml,
                        facadeAppForm.getPartnerNo(), transCode);
                logger.info("返回报文:" + responseXml);
                // 请求交易系统
                if (StrUtil.isNotEmpty(responseXml)) {
                    backmessage = getResponseXml(responseXml);
                    if (statusCode.equals(backmessage.get("RESULTCODE"))) {
                        // 修改投保单状态
                        FacadeAppForm uFacadeAppForm = new FacadeAppForm();
                        uFacadeAppForm.setAppNo(facadeAppForm.getAppNo());
                        uFacadeAppForm
                                .setUpdateTime(DateUtil.getNowTimestamp());

                        // 见费出单
                        if ("Y".equals(facadeTempPolicy.getIsSeeMoney())) {
                            if ("0".equals(cardInfo.getPricelessType())) {
                                uFacadeAppForm.setTradAppNo(backmessage
                                        .get("APPNO"));
                                uFacadeAppForm.setStatus(Constants.POL_STATUS_12);

                                facadeAppForm.setStatus(Constants.POL_STATUS_12);
                                facadeAppForm
                                        .setTradAppNo(backmessage.get("APPNO"));
                            } else {
                                uFacadeAppForm.setTradAppNo(backmessage
                                        .get("APPNO"));
                                uFacadeAppForm.setStatus(Constants.POL_STATUS_11);
    
                                facadeAppForm.setStatus(Constants.POL_STATUS_11);
                                facadeAppForm
                                        .setTradAppNo(backmessage.get("APPNO"));
                            }
                        } else {
                            uFacadeAppForm.setPolNo(backmessage.get("POLNO"));
                            uFacadeAppForm.setPolSubDate(DateUtil
                                    .getNowTimestamp());
                            uFacadeAppForm.setStatus(Constants.POL_STATUS_02);

                            facadeAppForm.setPolNo(backmessage.get("POLNO"));
                            facadeAppForm.setPol_No(backmessage.get("POLNO"));
                            facadeAppForm.setStatus(Constants.POL_STATUS_02);

                            // 非见费出单要记录投保轨迹表
                            // 维护轨迹表
                            FacadeHis facadeHis = new FacadeHis();
                            facadeHis.setSerialNumber(transNo);
                            facadeHis.setPolNo(backmessage.get("POLNO"));
                            facadeHis.setEffDate(facadeAppForm.getEffDate());
                            facadeHis.setTermDate(facadeAppForm.getTermDate());
                            facadeHis.setTradProdCode(facadeAppForm
                                    .getInsuPlanNo());
                            facadeHis
                                    .setPartnerNo(facadeAppForm.getPartnerNo());
                            facadeHis.setPartnerSaleNo(facadeAppForm
                                    .getPartneSaleNo());
                            facadeHis.setTotPremamt(facadeAppForm
                                    .getTotPremamt());
                            facadeHis.setEmpDept(facadeAppForm.getDeptNo());
                            facadeHis.setOperator(facadeTempPolicy
                                    .getOperator());
                            facadeHis
                                    .setPremNotax(facadeAppForm.getPremNotax());
                            facadeHis.setPremVat(facadeAppForm.getPremVat());
                            facadeHisService.saveFacadeHis(facadeHis);

                        }
                        String eUrl = backmessage.get("EURL");
                        if (StrUtil.isNotEmpty(eUrl)) {
                            uFacadeAppForm.seteUrl(eUrl);
                            facadeAppForm.seteUrl(eUrl);
                        }
                        facadeAppForm.setTradAppNo(backmessage.get("APPNO"));
                        facadeAppFormDao
                                .updateFacadeAppFormNotNull(uFacadeAppForm);
                        if (Constants.POL_STATUS_02.equals(uFacadeAppForm
                                .getStatus())) {
                            // 卡单表状态更新为已激活
                            //激活时间
                            cardInfo.setAppDate(DateUtil.getNowDate(
                                    "yyyy-MM-dd HH:mm:ss"));
                            //起保时间
                            cardInfo.setEffDate(DateUtil.parse(facadeAppForm.getStringEffDate(),
                                    "yyyy-MM-dd HH:mm:ss"));
                            cardInfoDao.updateCardStatus(cardInfo);
                        }
                    } else {
                        flag = false;
                    }
                    facadeAppForm.setResultCode(backmessage.get("RESULTCODE"));
                    facadeAppForm.setErrInfo(backmessage.get("ERRINFO"));

                    facadeTransLog.setResultCode(backmessage.get("RESULTCODE"));
                    facadeTransLog.setResultDesc(backmessage.get("ERRINFO"));
                } else {
                    logger.info("投保单号:[" + facadeAppForm.getAppNo()
                            + "]核保确认连接失败");
                    facadeTransLog.setResultCode("-1");
                    facadeTransLog.setResultDesc("连接失败");

                    facadeAppForm.setResultCode("-1");
                    facadeAppForm.setErrInfo("连接失败");
                    flag = false;
                }
            } catch (Exception e) {
                logger.error("系统异常", e);
            } finally {
                // 缴费后更新交易记录表信息
                facadeTransLog.setResponseXml(responseXml);
                facadeTransLog.setResponseDate(new Date());
                facadeAppFormDao.updateFacadeAppFormNotNull(facadeAppForm);
                facadePayDao.insertFacadeTransLog(facadeTransLog);
            }
            facadeAppForms.set(i, facadeAppForm);
        }
        // 如果投保单全部是都投保成功 修改暂存单状态为完成状态
        if (flag) {
            // 见费出单
            FacadeTempPolicy uFacadeTempPolicy = new FacadeTempPolicy();
            uFacadeTempPolicy.setStagNo(facadeTempPolicy.getStagNo());
            uFacadeTempPolicy.setUpdateTime(DateUtil.getNowTimestamp());
            if ("Y".equals(facadeTempPolicy.getIsSeeMoney())) {
                if ("0".equals(cardInfo.getPricelessType())) {
                    // 已支付
                    uFacadeTempPolicy.setStatus(Constants.POL_STATUS_12);
                } else {
                    // 待支付
                    uFacadeTempPolicy.setStatus(Constants.POL_STATUS_11);
                }
            } else {
                // 投保完成
                uFacadeTempPolicy.setStatus(Constants.POL_STATUS_02);
            }
            facadeTempPolicy.setStatus(uFacadeTempPolicy.getStatus());
            facadeTempPolicyDao.updateTempPolicyNotNull(uFacadeTempPolicy);
        }
        facadeTempPolicy.setFacadeAppForms(facadeAppForms);
        logger.info("暂存单:[" + facadeTempPolicy.getStagNo() + "]请求发送完成");
        return facadeTempPolicy;
    }

    /**
     * 
     * @param responseXml
     * @return 返回解析后的报文集合
     * @throws UnsupportedEncodingException
     */
    private Map<String, String> getResponseXml(String responseXml)
            throws UnsupportedEncodingException {
        Map<String, String> map = new HashMap<String, String>();
        try {
            Document document = DocumentHelper.parseText(responseXml);
            Element root = document.getRootElement();
            String RESULTCODE = root.selectSingleNode(
                    "/INSUREQRET/HEAD/RESULTCODE").getText();
            map.put("RESULTCODE", RESULTCODE);
            String ERRINFO = root.selectSingleNode("/INSUREQRET/HEAD/ERRINFO") != null ? root
                    .selectSingleNode("/INSUREQRET/HEAD/ERRINFO").getText()
                    : "";

            if (ERRINFO.getBytes("gbk").length > 300) {
                map.put("ERRINFO", ERRINFO.substring(0, 150));
            } else {
                map.put("ERRINFO", ERRINFO);
            }
            String TRANSRNO = root
                    .selectSingleNode("/INSUREQRET/HEAD/TRANSRNO") != null ? root
                    .selectSingleNode("/INSUREQRET/HEAD/TRANSRNO").getText()
                    : "";
            map.put("TRANSRNO", TRANSRNO);
            String APPNO = root.selectSingleNode("/INSUREQRET/MAIN/APPNO") != null ? root
                    .selectSingleNode("/INSUREQRET/MAIN/APPNO").getText() : "";
            map.put("APPNO", APPNO);
            String POLNO = root.selectSingleNode("/INSUREQRET/MAIN/POLNO") != null ? root
                    .selectSingleNode("/INSUREQRET/MAIN/POLNO").getText() : "";
            map.put("POLNO", POLNO);
            String EURL = root.selectSingleNode("/INSUREQRET/MAIN/EURL") != null ? root
                    .selectSingleNode("/INSUREQRET/MAIN/EURL").getText() : "";
            map.put("EURL", EURL);
        } catch (DocumentException e) {
            map.put("RESULTCODE", "-1");
            map.put("ERRINFO", "解析错误");
            logger.info("报文解析错误", e);
        }
        return map;
    }

    /**
     * 装配投被保受益人信息
     * 
     * @param facadeAppForm
     * @return
     * @throws Exception
     */
    private Map<String, Object> createFacadeAppForm(FacadeAppForm facadeAppForm)
            throws Exception {
        Map<String, String> map = new HashMap<String, String>();
        map.put("appNo", facadeAppForm.getAppNo());
        // 被保人列表
        List<FacadeAppInsured> facadeAppInsureds = facadeAppInsuredDao
                .selectFacadeAppInsuredList(map);

        // 投保人
        FacadeAppApplicant facadeAppApplicant = facadeAppApplicantDao
                .selectFacadeAppApplicantByMap(map);
        map = new HashMap<String, String>();
        //激活卡法定受益人
//        // 受益人列表
//        for (int i = 0; i < facadeAppInsureds.size(); i++) {
//            FacadeAppInsured facadeAppInsured = facadeAppInsureds.get(i);
//            map.put("insuredClientNo", facadeAppInsured.getClientNo());
//            List<FacadeAppBeneficiary> facadeAppBeneficiarys = facadeAppBeneficiaryDao
//                    .selectFacadeAppBeneficiaryByMap(map);
//            if (null != facadeAppBeneficiarys
//                    && facadeAppBeneficiarys.size() > 0) {
//                facadeAppInsured
//                        .setFacadeAppBeneficiarys(facadeAppBeneficiarys);
//                facadeAppInsureds.set(i, facadeAppInsured);
//            }
//        }
        // 处理投保单的完整状态
        Map<String, Object> mapParam = new HashMap<String, Object>();
        mapParam.put("facadeAppForm", facadeAppForm);
        mapParam.put("facadeAppInsureds", facadeAppInsureds);
        mapParam.put("facadeAppApplicant", facadeAppApplicant);
        return mapParam;
    }

    /**
     * 更新卡单表的暂存单号
     * 
     * @param cardNo
     * @param stagNo
     * @throws Exception
     */
    @Override
    public Result updateStagNoByCardNo(Map<String, String> param)
            throws Exception {
        return cardInfoDao.updateStagNoByCardNo(param);
    }
    
    /**
     * 有价激活卡缴费确认
     * @param cardNo
     * @throws Exception
     */
    @Override
    public Result updatePayCostCallBack(String cardNo,String commantId,
            String stagNo, BigDecimal singlePremamt) throws Exception {
    	
    	//与新支付平台对接，查新支付平台表 20190312 helina
    	CommamtFee commamtFee = new CommamtFee();
    	commamtFee = (CommamtFee) commamtFeeDao.query(
                "getCommamtInfoByCommamtId", commantId);
    	String isNewPay;
        isNewPay = commamtFee.getIsNewpay();//commamt_fee表中添加isNewPay字段判断是否为新支付平台
        
        if("0".equals(isNewPay)){//0代表为新支付平台
        	FacadeNewPayment newPayment = (FacadeNewPayment) facadenewPayDao.query(
                    "getFacadeNewPaymentByPayId", commantId);
        	if (newPayment == null) {
                return Result.error("此有价激活卡没有缴费信息");
            }
        	newPayment.setPayId(stagNo);
        	newPayment.setAmount(singlePremamt);//新支付平台表中金额字段改为amount
            return payService.updateNewPayCostCallBack(newPayment);
        	
        }else{//非0为老支付平台
        	//根据出库id查询缴费信息
            FacadePayment payment = (FacadePayment) facadePayDao.query(
                    "getFacadePaymentByPayId", commantId);
        	if (payment == null) {
                return Result.error("此有价激活卡没有缴费信息");
            }
            payment.setPayId(stagNo);
            payment.setTransAmt(singlePremamt);
            return payService.updatePayCostCallBack(payment);
        }
        //与新支付平台对接，查新支付平台表 20190312 helina end
        //FacadePayment payment = facadePayDao.getPayInfoBycardNo(cardNo);
//        FacadePayment payment = new FacadePayment();
//        payment.setTransNo("ZC20181000147810PAY0001");
//        payment.setMerId("808080112694512");
//        payment.setPayNo("1120180806000010");
//        payment.setPayId("ZC20181000147810");
//        payment.setPaymentType("11");
//        payment.setPayDate(DateUtil.parse("2018-08-06 16:56:15",
//                "yyyy-MM-dd HH:mm:ss"));
//        payment.setPayResult("0000");
////        payment.setPayRemark(payRemark);
//        payment.setFeedbackDate(new Date());
//        payment.setTransAmt(new BigDecimal("67.48"));
//        payment.setStatus("2");
        
//        if (payment == null) {
//            return Result.error("此卡号" + cardNo + "没有缴费信息");
//        }
    }
    
    /**
     * 根据投保单查询保单表,投保人信息表,被表信息表
     * 
     * @param appNo
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> queryAllObjectInfo(String appNo) {
        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, String> ms = new HashMap<String, String>();
        ms.put("appNo", appNo);
        // 投保单查询投保单信息
        FacadeAppForm facadeAppForm;
        try {
            facadeAppForm = facadeAppFormDao
                    .queryFacadeAppFormByAppNo(appNo);
            // 取得投保人信息
            FacadeAppApplicant facadeAppApplicant = (FacadeAppApplicant) facadeAppApplicantDao
                    .selectFacadeAppApplicantByMap(ms);
            // 取得被保人信息
            List<FacadeAppInsured> facadeAppInsureds = (List<FacadeAppInsured>) facadeAppInsuredDao
                    .selectFacadeAppInsuredByMap(ms);
            if(facadeAppForm == null) {
                return null;
            }
            if(facadeAppApplicant == null) {
                return null;
            }
            if(facadeAppInsureds == null) {
                return null;
            }
            // 时间处理
            String effDate = DateUtil.format(facadeAppForm.getEffDate(),
                    "yyyy-MM-dd ") + facadeAppForm.getEffHours() + ":00:00";
            String termDate = DateUtil.format(facadeAppForm.getTermDate(),
                    "yyyy-MM-dd ") + facadeAppForm.getTermHours() + ":00:00";
            map.put("effDate", effDate);
            map.put("termDate", termDate);
            // 没有受益人信息，默认是法定受益人
            map.put("facadeAppForm", facadeAppForm);
            map.put("facadeAppApplicant", facadeAppApplicant);
            map.put("facadeAppInsureds", facadeAppInsureds);
        } catch (Exception e) {
            logger.error(e);
            return null;
        }
        return map;
    }
    
    /**
     * 查询职业树结构
     * @return
     * @throws Exception
     */
    @Override
    public List<DictOccupDto> queryWorkTree(String id) throws Exception {
        return dictOccupDao.queryWorkTree(id);
    }
    
    /**
     * 批量投保保存
     * @param facadeAppForm
     * @param extend
     * @param facadeAppApplicant
     * @param facadeAppInsured
     * @param gatewayuser
     * @param cardNo
     * @param isSeeMoney
     * @param stagNo
     * @return
     * @throws Exception
     */
    @Override
    public Result saveBatchTemporary(FacadeAppForm facadeAppForm,
            FacadeAppFormExtend extend, FacadeAppApplicant facadeAppApplicant,
            FacadeAppInsured facadeAppInsured, GatewayUser gatewayuser,
            String cardNo, String isSeeMoney, String stagNo, int i)
                    throws Exception {
        CardInfo card = cardInfoDao.getCardInfoByCardNo(cardNo);
        if (null == card) {
            return Result.error("卡号："+cardNo+"不存在，请联系管理员！");
        }
        if (!Constants.ACTIR_CARD_ACTIVABLE.equals(card.getStatus())) {
            return Result.error("卡号："+cardNo+"不是可激活状态，不可激活！");
        }
        
        facadeAppForm.setDeptNo(card.getDeptNo());
        facadeAppForm.setInputEmp(card.getOperatorNo());
        FacadeTempPolicy facadeTempPolicy = new FacadeTempPolicy();
        // 查询合作伙伴和所属机构 PARTNER_NO DPT_CDE
        facadeTempPolicy.setPartnerNo(facadeAppForm.getPartnerNo());
        facadeTempPolicy.setPartnerSaleNo(facadeAppForm.getPartneSaleNo());
        facadeTempPolicy.setIsSeeMoney(isSeeMoney);
        if (StrUtil.isEmpty(stagNo)) {
            facadeTempPolicy.setStatus(Constants.POL_STATUS_01);
            facadeTempPolicy.setOperator(gatewayuser.getUserCode());
            facadeTempPolicy.setInputFate(new Date());
            facadeTempPolicy.setProdType(Constants.PROD_TYPE_NLIFE);
            // 添加暂存单
            facadeTempPolicy = facadeTempPolicyService
                    .saveFacadeTempPolicy(facadeTempPolicy);
            logger.info("暂存表插入成功");
            logger.info("暂存单:" + facadeTempPolicy.getStagNo() + "生成");
            stagNo = facadeTempPolicy.getStagNo();
        } else if (i == 0) {
            facadeTempPolicy.setIsSeeMoney(isSeeMoney);
            facadeTempPolicy.setStagNo(stagNo);
            facadeTempPolicy.setUpdateTime(DateUtil.getNowTimestamp());
            facadeTempPolicyDao.updateTempPolicyNotNull(facadeTempPolicy);
            logger.info("暂存表更新成功");
        }

        if (StrUtil.isEmpty(card.getAppNo())
                || !card.getStagNo().equals(stagNo)) {
            facadeAppForm.setStagNo(stagNo);
            // 添加投保单
            facadeAppForm = facadeAppFormService
                    .saveFacadeAppForm(facadeAppForm);
            logger.info("保单表插入成功");
            // 得到保单号
            String appNo = facadeAppForm.getAppNo();
            // 保存投保单扩展信息
            extend.setAppNo(appNo);
            facadeAppFormExtendDao.saveFacadeAppFormExtend(extend);
            logger.info("保单扩展表插入成功");

            facadeAppApplicant.setAppNo(appNo);
            logger.info("投保单:" + appNo + "生成");
            // 添加被保人
            if (facadeAppInsured != null) {
                facadeAppInsured.setAppNo(appNo);
                facadeAppInsuredService.saveFacadeAppInsured(facadeAppInsured);
            }
            logger.info("被保人表插入成功");
            // 添加投保人
            facadeAppApplicant = facadeAppApplicantService
                    .saveFacadeAppApplicant(facadeAppApplicant);
            logger.info("投保人表插入成功");
            logger.info("卡号："+cardNo+"对应的投保信息保存成功");
            
            // 卡单表更新暂存单号
            Map<String, String> paramMap = new HashMap<String, String>();
            paramMap.put("stagNo", stagNo);
            paramMap.put("appNo", appNo);
            paramMap.put("cardNo", cardNo);
            updateStagNoByCardNo(paramMap);
        } else{
            facadeAppForm.setStagNo(stagNo);
            facadeAppForm.setAppNo(card.getAppNo());
            extend.setAppNo(card.getAppNo());
            facadeAppApplicant.setAppNo(card.getAppNo());
            facadeAppFormDao.updateFacadeAppFormNotNull(facadeAppForm);
            logger.info("保单表更新成功");
            facadeAppFormExtendDao.updateFacadeAppFormExtend(extend);
            logger.info("保单扩展表更新成功");
            if (facadeAppInsured != null) {
                facadeAppInsured.setAppNo(card.getAppNo());
                facadeAppInsuredDao.updateAppInsuredNotNull(facadeAppInsured);
                logger.info("被保人表更新成功");
            }
            facadeAppApplicantDao
                    .updateFacadeAppApplicantNotNull(facadeAppApplicant);
            logger.info("投保人表更新成功");
            logger.info("卡号："+cardNo+"对应的投保信息更新成功");
        }
        
        return Result.success("暂存成功", stagNo);
    }
    
    /**
     * 查询暂存单下的投保单数
     * @param stagNo
     * @return
     * @throws Exception
     */
    @Override
    public int queryAppCountByStagNo(String stagNo) throws Exception {
        return facadeAppFormDao.queryAppCountByStagNo(stagNo);
    }
    
    /**
     * 查询暂存单下的卡总张数
     * @param stagNo
     * @return
     * @throws Exception
     */
    @Override
    public int queryCardCountByStagNo(String stagNo) throws Exception {
        return cardInfoDao.queryCardCountByStagNo(stagNo);
    }
    
    @Override
    public FacadeAppForm sendNlife1(FacadeAppForm facadeAppForm) throws Exception {
        FacadeTransLog facadeTransLog = null;
        Map<String, String> backmessage = null;
        String responseXml = null;
        //加载卡单信息
        CardInfo cardInfo = cardInfoDao.getCardInfoByAppNo(
                facadeAppForm.getAppNo(), facadeAppForm.getStagNo());
        //加载暂存单表
        FacadeTempPolicy facadeTempPolicy=new FacadeTempPolicy();
        facadeTempPolicy.setStagNo(facadeAppForm.getStagNo());
        facadeTempPolicy.setStatus("'01'");
        facadeTempPolicy=facadeTempPolicyService.findFacadeTempPolicy(facadeTempPolicy);
        String policyType=facadeTempPolicy.getProdType();
        boolean flag = true;

        String statusCode = "";// 成功返回状态
        String trialUrl = "";
        String trialTemplates = "";
        String transCode = "";
        if (StrUtil.isNotEmpty(policyType)) {
            if (Constants.PROD_TYPE_NLIFE.equals(policyType)) { // 新意健险
                trialUrl = Constants.NLIFE_TRIAL_URL;
                trialTemplates = Constants.NLIFE_T01_TEMPLATES;
                transCode = Constants.GW_CH_TX_T01;
                statusCode = Constants.NLIFE_STATUS_CODE;
            }
        }
        
        try {
            logger.info("投保单:[" + facadeAppForm.getAppNo() + "]");
            facadeTransLog = new FacadeTransLog();
            facadeAppForm.setStringEffDate(DateUtil.format(
                    facadeAppForm.getEffDate(), "yyyy-MM-dd")
                    + " "
                    + (facadeAppForm.getEffHours() == null ? "00"
                            : facadeAppForm.getEffHours() + ":00:00"));
            facadeAppForm.setStringTermDate(DateUtil.format(
                    facadeAppForm.getTermDate(), "yyyy-MM-dd")
                    + " "
                    + (facadeAppForm.getTermHours() == null ? "00"
                            : facadeAppForm.getTermHours() + ":00:00"));
            facadeAppForm.setUrlApp_No(facadeAppForm.getAppNo());

            // 生成交易流水id
            String transNo = payService
                    .generateTradingSerialNumber(facadeAppForm.getAppNo());
            // 装配投保单数据
            Map<String, Object> map = createFacadeAppForm(facadeAppForm);
//            List<FacadeAppInsured> FacadeAppInsureds = (List<FacadeAppInsured>) map
//                    .get("facadeAppInsureds");
//            for (int m = 0; m < FacadeAppInsureds.size(); m++) {
//                List<FacadeAppBeneficiary> facadeAppBeneficiarys = FacadeAppInsureds
//                        .get(m).getFacadeAppBeneficiarys();
//                if (facadeAppBeneficiarys != null) {
//                    for (int n = 0; n < facadeAppBeneficiarys.size(); n++) {
//                        facadeAppBeneficiarys
//                                .get(n)
//                                .setBenePct(
//                                        String.valueOf((int) (Float
//                                                .parseFloat(facadeAppBeneficiarys
//                                                        .get(n)
//                                                        .getBenePct()) * 100)));
//                    }
//                }
//            }
            map.put("SERIALNUMBER", transNo);
            map.put("USERCODE", facadeTempPolicy.getOperator());

            map.put("CURRENCY", "CNY");
            map.put("BENIFITPRINTFLAG", "1");
            FacadeAppFormExtend extend = exchenge(facadeAppFormExtendDao
                    .queryFacadeAppFormExtend(facadeAppForm
                            .getAppNo()));
            map.put("facadeAppFormExtend",extend);
            map.put("cardNo", cardInfo.getCardNo());
            map.put("isSeeMoney", facadeTempPolicy.getIsSeeMoney());
            String requestXml = FreeMarkerUtil.process(
                    Config.getProp(trialTemplates), map);
            logger.info("请求报文:" + requestXml);
            // 发送请求时插入交易表
            facadeTransLog.setTransNo(transNo);
            facadeTransLog.setTransCode(transCode);
            facadeTransLog.setStagNo(facadeTempPolicy.getStagNo());
            facadeTransLog.setAppNo(facadeAppForm.getAppNo());
            // 添加交易表
            facadeTransLog.setRequestXml(requestXml);
            facadeTransLog.setRequestDate(new Date());
            responseXml = HttpConnectorUtil.interaction(
                    Config.getProp(trialUrl), requestXml,
                    facadeAppForm.getPartnerNo(), transCode);
            logger.info("返回报文:" + responseXml);
            // 请求交易系统
            if (StrUtil.isNotEmpty(responseXml)) {
                backmessage = getResponseXml(responseXml);
                if (statusCode.equals(backmessage.get("RESULTCODE"))) {
                    // 修改投保单状态
                    FacadeAppForm uFacadeAppForm = new FacadeAppForm();
                    uFacadeAppForm.setAppNo(facadeAppForm.getAppNo());
                    uFacadeAppForm
                            .setUpdateTime(DateUtil.getNowTimestamp());

                    // 见费出单
                    if ("Y".equals(facadeTempPolicy.getIsSeeMoney())) {
                        if ("0".equals(cardInfo.getPricelessType())) {
                            uFacadeAppForm.setTradAppNo(backmessage
                                    .get("APPNO"));
                            uFacadeAppForm.setStatus(Constants.POL_STATUS_12);

                            facadeAppForm.setStatus(Constants.POL_STATUS_12);
                            facadeAppForm
                                    .setTradAppNo(backmessage.get("APPNO"));
                        } else {
                            uFacadeAppForm.setTradAppNo(backmessage
                                    .get("APPNO"));
                            uFacadeAppForm.setStatus(Constants.POL_STATUS_11);

                            facadeAppForm.setStatus(Constants.POL_STATUS_11);
                            facadeAppForm
                                    .setTradAppNo(backmessage.get("APPNO"));
                        }
                    } else {
                        uFacadeAppForm.setPolNo(backmessage.get("POLNO"));
                        uFacadeAppForm.setPolSubDate(DateUtil
                                .getNowTimestamp());
                        uFacadeAppForm.setStatus(Constants.POL_STATUS_02);

                        facadeAppForm.setPolNo(backmessage.get("POLNO"));
                        facadeAppForm.setPol_No(backmessage.get("POLNO"));
                        facadeAppForm.setStatus(Constants.POL_STATUS_02);

                        // 非见费出单要记录投保轨迹表
                        // 维护轨迹表
                        FacadeHis facadeHis = new FacadeHis();
                        facadeHis.setSerialNumber(transNo);
                        facadeHis.setPolNo(backmessage.get("POLNO"));
                        facadeHis.setEffDate(facadeAppForm.getEffDate());
                        facadeHis.setTermDate(facadeAppForm.getTermDate());
                        facadeHis.setTradProdCode(facadeAppForm
                                .getInsuPlanNo());
                        facadeHis
                                .setPartnerNo(facadeAppForm.getPartnerNo());
                        facadeHis.setPartnerSaleNo(facadeAppForm
                                .getPartneSaleNo());
                        facadeHis.setTotPremamt(facadeAppForm
                                .getTotPremamt());
                        facadeHis.setEmpDept(facadeAppForm.getDeptNo());
                        facadeHis.setOperator(facadeTempPolicy
                                .getOperator());
                        facadeHis
                                .setPremNotax(facadeAppForm.getPremNotax());
                        facadeHis.setPremVat(facadeAppForm.getPremVat());
                        facadeHisService.saveFacadeHis(facadeHis);

                    }
                    String eUrl = backmessage.get("EURL");
                    if (StrUtil.isNotEmpty(eUrl)) {
                        uFacadeAppForm.seteUrl(eUrl);
                        facadeAppForm.seteUrl(eUrl);
                    }
                    facadeAppForm.setTradAppNo(backmessage.get("APPNO"));
                    facadeAppFormDao
                            .updateFacadeAppFormNotNull(uFacadeAppForm);
                    if (Constants.POL_STATUS_02.equals(uFacadeAppForm
                            .getStatus())) {
                        // 卡单表状态更新为已激活
                        //激活时间
                        cardInfo.setAppDate(DateUtil.getNowDate(
                                "yyyy-MM-dd HH:mm:ss"));
                        //起保时间
                        cardInfo.setEffDate(DateUtil.parse(facadeAppForm.getStringEffDate(),
                                "yyyy-MM-dd HH:mm:ss"));
                        cardInfoDao.updateCardStatus(cardInfo);
                    }
                } else {
                    flag = false;
                }
                facadeAppForm.setResultCode(backmessage.get("RESULTCODE"));
                facadeAppForm.setErrInfo(backmessage.get("ERRINFO"));

                facadeTransLog.setResultCode(backmessage.get("RESULTCODE"));
                facadeTransLog.setResultDesc(backmessage.get("ERRINFO"));
            } else {
                logger.info("投保单号:[" + facadeAppForm.getAppNo()
                        + "]核保确认连接失败");
                facadeTransLog.setResultCode("-1");
                facadeTransLog.setResultDesc("连接失败");

                facadeAppForm.setResultCode("-1");
                facadeAppForm.setErrInfo("连接失败");
                flag = false;
            }
        } catch (Exception e) {
            logger.error("系统异常", e);
        } finally {
            // 缴费后更新交易记录表信息
            facadeTransLog.setResponseXml(responseXml);
            facadeTransLog.setResponseDate(new Date());
            facadeAppFormDao.updateFacadeAppFormNotNull(facadeAppForm);
            facadePayDao.insertFacadeTransLog(facadeTransLog);
        }
        // 如果投保单全部是都投保成功 修改暂存单状态为完成状态
        if (flag) {
            FacadeAppForm sfacadeAppForm =new FacadeAppForm();
            sfacadeAppForm.setStagNo(facadeAppForm.getStagNo());
            sfacadeAppForm.setStatus("'01'");
            List<FacadeAppForm> facadeAppForms=facadeAppFormDao.selectFacadeAppFormList(sfacadeAppForm);
            if(facadeAppForms.size()==0){
                // 见费出单
                FacadeTempPolicy uFacadeTempPolicy = new FacadeTempPolicy();
                uFacadeTempPolicy.setStagNo(facadeTempPolicy.getStagNo());
                uFacadeTempPolicy.setUpdateTime(DateUtil.getNowTimestamp());
                if ("Y".equals(facadeTempPolicy.getIsSeeMoney())) {
                    if ("0".equals(cardInfo.getPricelessType())) {
                        // 已支付
                        uFacadeTempPolicy.setStatus(Constants.POL_STATUS_12);
                    } else {
                        // 待支付
                        uFacadeTempPolicy.setStatus(Constants.POL_STATUS_11);
                    }
                } else {
                    // 投保完成
                    uFacadeTempPolicy.setStatus(Constants.POL_STATUS_02);
                }
                facadeTempPolicy.setStatus(uFacadeTempPolicy.getStatus());
                facadeTempPolicyDao.updateTempPolicyNotNull(uFacadeTempPolicy);
            }
        }
        logger.info("投保单:["+facadeAppForm.getAppNo()+"]核保确认处理结束");
        return facadeAppForm;
    }
    
    /**
     * 批量投保页面删除投保信息
     * @param cardNo
     * @param stagNo
     * @return
     * @throws Exception
     */
    @Override
    public Result deleteAppInfo(String cardNo, String stagNo) throws Exception {
        
        // 取得暂存单信息
        FacadeTempPolicy facadeTempPolicy = facadeTempPolicyDao
                .queryFacadeTempPolicyById(stagNo);
        if (facadeTempPolicy == null){
            return Result.error("暂存单号："+stagNo+"下没有暂存单信息");
        } else if (!"01".equals(facadeTempPolicy.getStatus())) {
            return Result.error("暂存单号："+stagNo+"对应的暂存单状态为非录入，不可删除！");
        }
        CardInfo cardInfo = cardInfoDao.getCardInfoByCardNo(cardNo);
        String appNo = cardInfo.getAppNo();
        //投保单信息删除
        boolean flag = facadeAppFormDao.deleteAppFormByAppNo(appNo);
        if (!flag) {
            return Result.error("投保单信息删除失败");
        }
        //投保扩展表信息删除
        facadeAppFormExtendDao.deleteAppFormExtendByAppNo(appNo);
        //投保人表信息删除
        facadeAppApplicantDao.deleteAppApplicantByAppno(appNo);
        //被保人表信息删除
        facadeAppInsuredDao.deleteInsuredByAppNo(appNo);
        //更新卡单表
        Map<String, String> paramMap = new HashMap<String, String>();
        paramMap.put("cardNo", cardNo);
        paramMap.put("stagNo", null);
        paramMap.put("appNo", null);
        Result result = cardInfoDao.updateStagNoByCardNo(paramMap);
        if ("success".equals(result.getResult())) {
            return Result.success("卡单信息更新成功");
        }else{
            return Result.error("卡单信息更新失败");
        }
    }
    
    /**
     * 批量投保信息取得
     * @param stagNo
     * @return
     * @throws exception
     */
    @Override
    public List<ActirationCardBatchInsuredDto> getBatchInsuredList(
            String stagNo) throws Exception {
        
        return actirationCardBatchInsuredDao.getBatchInsuredList(stagNo);
    }
    
    /**
     * 删除批量投保信息
     * @param stagNo
     * @return
     * @throws Exception
     */
    @Override
    public Result deleteBatchByStagNo(String stagNo) throws Exception {
        //置空卡单表中的暂存单号和投保单号
        Map<String, String> map = new HashMap<String, String>();
        map.put("stagNo", stagNo);
        map.put("appNo", null);
        map.put("stagNoAft", null);
        map.put("status", Constants.ACTIR_CARD_ACTIVABLE);
        boolean flag = cardInfoDao.updateStagNoAndAppNoByStagNo(map);
        if (!flag) {
            return Result.error("暂存单号："+stagNo+"下没有可激活的卡单信息，此卡号可以继续投保");
        }
        //删除投保单信息
        actirationCardBatchInsuredDao.deleteBatchInfos(stagNo);
        return Result.success("卡单下的投保信息删除成功，可重新投保!");
        
    }
    
    /**
	 * 更新暂存单表和投保单表状态（12：已支付） helina 20190425
	 * @param stagNo
	 * @return
	 * @throws Exception
	 */
    public boolean afterPayUpdateStatus(String stagNo) throws Exception {
    	Boolean flag = false;
    	//更新投保单表
    	FacadeAppForm uFacadeAppForm = new FacadeAppForm();
    	uFacadeAppForm.setStagNo(stagNo);
    	uFacadeAppForm.setStatus(Constants.POL_STATUS_12);
    	flag = facadeAppFormDao.updateFacadeAppFormStatus12(uFacadeAppForm);
    	if (flag) {
        	//更新暂存单表
        	FacadeTempPolicy uFacadeTempPolicy = new FacadeTempPolicy();
        	uFacadeTempPolicy.setStagNo(stagNo);
        	uFacadeTempPolicy.setStatus(Constants.POL_STATUS_12);
        	return facadeTempPolicyDao.updateFacadeTempPolicyStatus12(uFacadeTempPolicy);
    	}else {
    		return flag;
    	}
    }
    /**
   	 * 车辆性质转换
   	 * @param FacadeAppFormExtend
   	 */
    public FacadeAppFormExtend exchenge(FacadeAppFormExtend extend){
    	if("602057111".equals(extend.getUseNature())){
    		extend.setUseNature("004");
    	}else if("602057112".equals(extend.getUseNature())){
    		extend.setUseNature("002");
    	}else if("602057113".equals(extend.getUseNature())){
    		extend.setUseNature("001");
    	}else if("602057114".equals(extend.getUseNature())){
    		extend.setUseNature("003");
    	}
    	return extend;
    }

	@Override
	public List<InputStream> getFileInputStream(MultipartFile[] myfiles)
			throws Exception {
		List<InputStream> inputStreams=new ArrayList<InputStream>();
		try {
			for(MultipartFile file : myfiles){
				String fileNameStr = file.getOriginalFilename();
				int lastDotIndex = fileNameStr.lastIndexOf(".");
				if(-1==lastDotIndex){
					throw new AppException("请选择扩展名为 .xls 的文件上传");
				}else{
					String extName = fileNameStr.substring(lastDotIndex+1,fileNameStr.length());
					if(!"xls".equals(extName.toLowerCase())){
						throw new AppException("请选择扩展名为 .xls 的文件上传");
					}
				}
				if (file.isEmpty()) {
					throw new AppException("上传失败，上传文件不能为空！");
				} else {
					InputStream inputStream = file.getInputStream();
					inputStreams.add(inputStream);
				}
			}
		} catch (IOException e) {
			logger.info("上传错误!请稍后重试",e);
			throw new AppException("上传错误!请稍后重试");
		}
		return inputStreams;
	}

	@Override
	public Map<String, Object> insertDataToPage(InputStream inputStream,String riskMainType,String cardDefineId) throws Exception {
		HSSFWorkbook workBook = null;
		Map<String, Object> map = new HashMap<String, Object>();
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		List<DictDto> relaList = DictCache.getDict("NLife_Rela");
		List<DictDto> idTypeList = DictCache.getDict("NLife_Id_Type");
		List<DictDto> occupationCodeList = DictCache.getDict("OccupationCode1");
		List<DictDto> userNatureList = DictCache.getDict("rule_template_useNature");
		List<DictDto> carNatureList = DictCache.getDict("rule_template_carNature");
		
		int lastRow = 0;
		//无riskMainType为个意险
		if (riskMainType==null||"".equals(riskMainType)) {
			riskMainType = "999";
		}
		try {
			workBook = new HSSFWorkbook(inputStream);
		} catch (Exception e) {
			map.put("errorMsg", "上传失败，上传文件内容有误!");
			return map;
		}
		HSSFSheet sheet = workBook.getSheetAt(0);
		String sheetName = workBook.getSheetName(0);
		if (sheet != null) {
			lastRow = sheet.getLastRowNum();
		}
		if (lastRow > 1) {
			int count = sheet.getLastRowNum();
			
			//驾乘险
			if ("2".equals(riskMainType)) {
				
				for (int rowNum = 2; rowNum <= count; rowNum++) {
					HSSFRow hssfRow = sheet.getRow(rowNum);
					if (hssfRow != null) {
						for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
							//是否为驾乘险必填项
							int[] arr1 = {0,1,2,3,4,5,6,9,24,25};
							int[] arr2 = {14,15,16,17,18,19,20,21};
							int a =  Arrays.binarySearch(arr1, colNum);
							int b =  Arrays.binarySearch(arr2, colNum);
							if ((a>=0)&&(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
								map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行数据中第"+(colNum+1)+"列数据为必录项不允许为空，请检查 ");
								return map;
							}
							if ((b>=0)&&(StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
								map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行数据中第"+(colNum+1)+"列数据当为驾乘险时被保人信息不可填，请检查 ");
								return map;
							}
						}
						//卡号与密码
						String cardNo = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
						String cardPw = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
						// 验证卡号密码是否正确
				        String checkMsg = checkExcelCard(cardNo,cardPw,cardDefineId,rowNum);
				        if (checkMsg!=null) {
							map.put("errorMsg", checkMsg);
						}
						//车牌号与车架号必填其一
				        String plateNum = ExcelUtil.getCellText(hssfRow.getCell(22)).trim();
				        String chassisNum = ExcelUtil.getCellText(hssfRow.getCell(23)).trim();
						if (StrUtil.isEmpty(plateNum)&&StrUtil.isEmpty(chassisNum)) {
							map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行数据驾乘险产品车牌号与车架号必填其一，请检查 ");
							return map;
						}
						//获取终保日期
						Date eDate = hssfRow.getCell(2).getDateCellValue();
						Calendar calendar = Calendar.getInstance();
				        calendar.setTime(eDate);
				        calendar.add(Calendar.YEAR, 1);
				        calendar.add(Calendar.DATE, -1);
				        Date tDate = calendar.getTime();
				        String termDate = DateUtil.format(tDate,"yyyy-MM-dd")+" 24:00:00";
				        String effDate = DateUtil.format(eDate,"yyyy-MM-dd HH:mm:ss");
				        //投保人信息
				        String tbrName = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
				        //投保人性质
				        String tbrNature = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
				        tbrNature = chengeTbrNature(tbrNature);
				        String tbrIdType = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
				        for (DictDto dto : idTypeList) {
			        		if (tbrIdType.equals(dto.getCodeName())) {
			        			tbrIdType = dto.getCode();
			        		}
			        	}
				        if(!checkTypeId(tbrNature,tbrIdType,idTypeList)){
				        	map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行投保人性质与投保人证件类型不匹配，请检查 ");
							return map;
				        }
				        String tbrIdNo = ExcelUtil.getCellText(hssfRow.getCell(6)).trim();
				        String tbrBir = "";
				        if (hssfRow.getCell(7)!=null) {
				        	tbrBir = DateUtil.format(hssfRow.getCell(7).getDateCellValue(),"yyyy-MM-dd");
						}else{
							tbrBir = IdCardUtil.getBirthdayFromCard(tbrIdNo);
						}
				        String tbrSex = ExcelUtil.getCellText(hssfRow.getCell(8)).trim();
				        if ("男".equals(tbrSex)) {
				        	tbrSex = "1";
						}else if ("女".equals(tbrSex)){
							tbrSex = "2";
						}
				        if (StrUtil.isEmpty(tbrSex)) {
				        	tbrSex = IdCardUtil.getIdSexNew(tbrIdNo);
						}
				        String tbrTel = ExcelUtil.getCellText(hssfRow.getCell(9)).trim();
				        String tbrEmail = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
				        String tbrNationality = ExcelUtil.getCellText(hssfRow.getCell(11)).trim();
				        if (StrUtil.isEmpty(tbrNationality)) {
				        	tbrNationality = "CHN";
						}
				        String tbrWorkName = ExcelUtil.getCellText(hssfRow.getCell(12)).trim();
				        String relWithInsured = ExcelUtil.getCellText(hssfRow.getCell(13)).trim();
				        for (DictDto dto : relaList) {
							if (relWithInsured.equals(dto.getCodeName())) {
								relWithInsured = dto.getCode();
							}
						}
				        String seatNum = ExcelUtil.getCellText(hssfRow.getCell(24)).trim();
				        String useNature = ExcelUtil.getCellText(hssfRow.getCell(25)).trim();
				        for (DictDto userNatureDto : userNatureList) {
				        	if (useNature.equals(userNatureDto.getCodeName())) {
				        		useNature = userNatureDto.getCode();
							}
						}
				        
						HashMap<String, Object> listMap = new HashMap<String, Object>();
						listMap.put("cardNo", cardNo);
						listMap.put("cardPw", cardPw);
						listMap.put("effDate", effDate);
						listMap.put("termDate", termDate);
						listMap.put("tbrName", tbrName);
						listMap.put("tbrNature", tbrNature);
						listMap.put("tbrIdType", tbrIdType);
						listMap.put("tbrIdNo", tbrIdNo);
						listMap.put("tbrBir", tbrBir);
						listMap.put("tbrSex", tbrSex);
						listMap.put("tbrTel", tbrTel);
						listMap.put("tbrEmail", tbrEmail);
						listMap.put("tbrNationality", tbrNationality);
						listMap.put("tbrWorkName", tbrWorkName);
						listMap.put("relWithInsured", relWithInsured);
						listMap.put("plateNum", plateNum);
						listMap.put("chassisNum", chassisNum);
						listMap.put("seatNum", seatNum);
						listMap.put("useNature", useNature);
						dataList.add(listMap);
					}
				}
				map.put("dataList", dataList);
				return map;
			}
			//驾意险
			else if ("9".equals(riskMainType)) {
				for (int rowNum = 2; rowNum <= count; rowNum++) {
					HSSFRow hssfRow = sheet.getRow(rowNum);
					if (hssfRow != null) {
						for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
							//是否为驾意险必填项
							int[] arr = {0,1,2,3,4,5,6,9,13,26};
							int a =  Arrays.binarySearch(arr, colNum);
							if ((a>=0)&&(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
								map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行数据中第"+(colNum+1)+"列数据为必录项不允许为空，请检查 ");
								return map;
							}
						}
						//当被保人不是本人时 被保人信息必填
						String relWithInsured = ExcelUtil.getCellText(hssfRow.getCell(13)).trim();
				        for (DictDto dto : relaList) {
							if (relWithInsured.equals(dto.getCodeName())) {
								relWithInsured = dto.getCode();
							}
						}
						//卡号与密码
						String cardNo = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
						String cardPw = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
						// 验证卡号密码是否正确
						String checkMsg = checkExcelCard(cardNo,cardPw,cardDefineId,rowNum);
				        if (checkMsg!=null) {
							map.put("errorMsg", checkMsg);
						}
				        
						//获取终保日期
						Date eDate = hssfRow.getCell(2).getDateCellValue();
						Calendar calendar = Calendar.getInstance();
				        calendar.setTime(eDate);
				        calendar.add(Calendar.YEAR, 1);
				        calendar.add(Calendar.DATE, -1);
				        Date tDate = calendar.getTime();
				        String termDate = DateUtil.format(tDate,"yyyy-MM-dd")+" 24:00:00";
				        String effDate = DateUtil.format(eDate,"yyyy-MM-dd HH:mm:ss");
				        //投保人信息
				        String tbrName = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
				        String tbrNature = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
				        tbrNature = chengeTbrNature(tbrNature);
				        String tbrIdType = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
			        	for (DictDto dto : idTypeList) {
			        		if (tbrIdType.equals(dto.getCodeName())) {
			        			tbrIdType = dto.getCode();
			        		}
			        	}
			        	if(!checkTypeId(tbrNature,tbrIdType,idTypeList)){
				        	map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行投保人性质与投保人证件类型不匹配，请检查 ");
							return map;
				        }
				        String tbrIdNo = ExcelUtil.getCellText(hssfRow.getCell(6)).trim();
				        String tbrBir = "";
				        if (hssfRow.getCell(7)!=null) {
				        	tbrBir = DateUtil.format(hssfRow.getCell(7).getDateCellValue(),"yyyy-MM-dd");
						}else{
							tbrBir = IdCardUtil.getBirthdayFromCard(tbrIdNo);
						}
				        String tbrSex = ExcelUtil.getCellText(hssfRow.getCell(8)).trim();
				        if ("男".equals(tbrSex)) {
				        	tbrSex = "1";
						}else if ("女".equals(tbrSex)){
							tbrSex = "2";
						}
				        if (StrUtil.isEmpty(tbrSex)&&"01".equals(tbrIdType)) {
				        	tbrSex = IdCardUtil.getIdSexNew(tbrIdNo);
						}
				        String tbrTel = ExcelUtil.getCellText(hssfRow.getCell(9)).trim();
				        String tbrEmail = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
				        String tbrNationality = ExcelUtil.getCellText(hssfRow.getCell(11)).trim();
				        if (StrUtil.isEmpty(tbrNationality)) {
				        	tbrNationality = "CHN";
						}
				        String tbrWorkType = ExcelUtil.getCellText(hssfRow.getCell(12)).trim();
				        String tbrWorkName = "";
						for (DictDto occDto : occupationCodeList) {
							if (tbrWorkType.equals(occDto.getCode())) {
								tbrWorkName = occDto.getCodeName();
							}
						}				        
				        //被保人信息
				        String bbrName = ExcelUtil.getCellText(hssfRow.getCell(14)).trim();;
			        	String bbrIdType = ExcelUtil.getCellText(hssfRow.getCell(15)).trim();
			        	for (DictDto dto : idTypeList) {
			        		if (bbrIdType.equals(dto.getCodeName())) {
			        			bbrIdType = dto.getCode();
			        		}
			        	}
			        	String bbrIdNo = ExcelUtil.getCellText(hssfRow.getCell(16)).trim();
			        	String bbrBir="";
						if (hssfRow.getCell(17)!=null) {
							bbrBir = DateUtil.format(hssfRow.getCell(17).getDateCellValue(),"yyyy-MM-dd");
						}else{
							bbrBir = IdCardUtil.getBirthdayFromCard(bbrIdNo);
						}
			        	String bbrSex = ExcelUtil.getCellText(hssfRow.getCell(18)).trim();
			        	if ("男".equals(bbrSex)) {
							bbrSex = "1";
						}else if ("女".equals(bbrSex)){
							bbrSex = "2";
						}
			        	if (StrUtil.isEmpty(tbrSex)&&"01".equals(bbrIdType)&&!("01".equals(relWithInsured))) {
				        	bbrSex = IdCardUtil.getIdSexNew(bbrIdNo);
						}
			        	String bbrTel = ExcelUtil.getCellText(hssfRow.getCell(19)).trim();
			        	String bbrNationality = ExcelUtil.getCellText(hssfRow.getCell(20)).trim();
			        	if (StrUtil.isEmpty(bbrNationality)) {
			        		bbrNationality = tbrNationality;
						}
			        	String bbrWorkType = ExcelUtil.getCellText(hssfRow.getCell(21)).trim();
				        String bbrWorkName = "";
						for (DictDto occDto : occupationCodeList) {
							if (bbrWorkType.equals(occDto.getCode())) {
								bbrWorkName = occDto.getCodeName();
							}
						}				        /*被保人信息赋值       */
				        if ("01".equals(relWithInsured)) {
				        	//如果为本人并且未填写被保人信息则同投保人
				        	if (StrUtil.isEmpty(bbrName)) {
								bbrName = tbrName;
							}
				        	if (StrUtil.isEmpty(bbrIdType)) {
				        		bbrIdType = tbrIdType;
							}
				        	if (StrUtil.isEmpty(bbrIdNo)) {
				        		bbrIdNo = tbrIdNo;
							}
				        	if (StrUtil.isEmpty(bbrTel)) {
				        		bbrTel = tbrTel;
							}
				        	if (StrUtil.isEmpty(bbrNationality)) {
				        		bbrNationality = tbrNationality;
							}
							if (StrUtil.isEmpty(bbrBir)) {
								bbrBir = tbrBir;
							}
							if (StrUtil.isEmpty(bbrSex)){
								bbrSex = tbrSex;
							}
							if (StrUtil.isEmpty(bbrWorkType)){
								bbrWorkType = tbrWorkType;
							}
							if (StrUtil.isEmpty(bbrWorkName)){
								bbrWorkName = tbrWorkName;
							}
						}else{
							for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
								int[] arr = {14,15,16};
								int a =  Arrays.binarySearch(arr, colNum);
								if ((a>=0)&&(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
									map.put("errorMsg", "导入失败：当投保人与被保险人关系非本人时，第" + (rowNum + 1)+ "行数据中第"+(colNum+1)+"列数据为必录项不允许为空，请检查 ");
									return map;
								}
							}
						}
				        String vehicleNature = ExcelUtil.getCellText(hssfRow.getCell(26)).trim();
				        for (DictDto carNatureDto : carNatureList) {
				        	if (vehicleNature.equals(carNatureDto.getCodeName())) {
				        		vehicleNature = carNatureDto.getCode();
							}
						}

						HashMap<String, Object> listMap = new HashMap<String, Object>();
				        listMap.put("tbrNature", tbrNature);
						listMap.put("cardNo", cardNo);
						listMap.put("cardPw", cardPw);
						listMap.put("effDate", effDate);
						listMap.put("termDate", termDate);
						listMap.put("tbrName", tbrName);
						listMap.put("tbrIdType", tbrIdType);
						listMap.put("tbrIdNo", tbrIdNo);
						listMap.put("tbrBir", tbrBir);
						listMap.put("tbrSex", tbrSex);
						listMap.put("tbrTel", tbrTel);
						listMap.put("tbrEmail", tbrEmail);
						listMap.put("tbrNationality", tbrNationality);
						listMap.put("tbrWorkType", tbrWorkType);
						listMap.put("tbrWorkName", tbrWorkName);
						listMap.put("relWithInsured", relWithInsured);
						listMap.put("bbrName", bbrName);
						listMap.put("bbrIdType", bbrIdType);
						listMap.put("bbrIdNo", bbrIdNo);
						listMap.put("bbrBir", bbrBir);
						listMap.put("bbrSex", bbrSex);
						listMap.put("bbrTel", bbrTel);
						listMap.put("bbrNationality", bbrNationality);
						listMap.put("bbrWorkType", bbrWorkType);
						listMap.put("bbrWorkName", bbrWorkName);
						listMap.put("vehicleNature", vehicleNature);
						dataList.add(listMap);
					}
				}
				map.put("dataList", dataList);
				return map;
			}
			//个意险
			else if ("999".equals(riskMainType)) {
				for (int rowNum = 2; rowNum <= count; rowNum++) {
					HSSFRow hssfRow = sheet.getRow(rowNum);
					if (hssfRow != null) {
						for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
							//是否为驾意险必填项
							int[] arr = {0,1,2,3,4,5,6,9,13,26};
							int a =  Arrays.binarySearch(arr, colNum);
							if ((a>=0)&&(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
								map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行数据中第"+(colNum+1)+"列数据为必录项不允许为空，请检查 ");
								return map;
							}
						}
						//当被保人不是本人时 被保人信息必填
						String relWithInsured = ExcelUtil.getCellText(hssfRow.getCell(13)).trim();
				        for (DictDto dto : relaList) {
							if (relWithInsured.equals(dto.getCodeName())) {
								relWithInsured = dto.getCode();
							}
						}
						//卡号与密码
						String cardNo = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
						String cardPw = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
						// 验证卡号密码是否正确
						String checkMsg = checkExcelCard(cardNo,cardPw,cardDefineId,rowNum);
				        if (checkMsg!=null) {
							map.put("errorMsg", checkMsg);
						}
				        
						//获取终保日期
						Date eDate = hssfRow.getCell(2).getDateCellValue();
						Calendar calendar = Calendar.getInstance();
				        calendar.setTime(eDate);
				        calendar.add(Calendar.YEAR, 1);
				        calendar.add(Calendar.DATE, -1);
				        Date tDate = calendar.getTime();
				        String termDate = DateUtil.format(tDate,"yyyy-MM-dd")+" 24:00:00";
				        String effDate = DateUtil.format(eDate,"yyyy-MM-dd HH:mm:ss");
				        //投保人信息
				        String tbrName = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
				        String tbrNature = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
				        tbrNature = chengeTbrNature(tbrNature);
				        String tbrIdType = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
			        	for (DictDto dto : idTypeList) {
			        		if (tbrIdType.equals(dto.getCodeName())) {
			        			tbrIdType = dto.getCode();
			        		}
			        	}
			        	 if(!checkTypeId(tbrNature,tbrIdType,idTypeList)){
					        	map.put("errorMsg", "导入失败：第" + (rowNum + 1)+ "行投保人性质与投保人证件类型不匹配，请检查 ");
								return map;
					        }
				        String tbrIdNo = ExcelUtil.getCellText(hssfRow.getCell(6)).trim();
				        String tbrBir = "";
				        if (hssfRow.getCell(7)!=null) {
				        	tbrBir = DateUtil.format(hssfRow.getCell(7).getDateCellValue(),"yyyy-MM-dd");
						}else{
							tbrBir = IdCardUtil.getBirthdayFromCard(tbrIdNo);
						}
				        String tbrSex = ExcelUtil.getCellText(hssfRow.getCell(8)).trim();
				        if ("男".equals(tbrSex)) {
				        	tbrSex = "1";
						}else if ("女".equals(tbrSex)){
							tbrSex = "2";
						}
				        if (StrUtil.isEmpty(tbrSex)&&"01".equals(tbrIdType)) {
				        	tbrSex = IdCardUtil.getIdSexNew(tbrIdNo);
						}
				        String tbrTel = ExcelUtil.getCellText(hssfRow.getCell(9)).trim();
				        String tbrEmail = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
				        String tbrNationality = ExcelUtil.getCellText(hssfRow.getCell(11)).trim();
				        if (StrUtil.isEmpty(tbrNationality)) {
				        	tbrNationality = "CHN";
						}
				        String tbrWorkType = ExcelUtil.getCellText(hssfRow.getCell(12)).trim();
				        String tbrWorkName = "";
						for (DictDto occDto : occupationCodeList) {
							if (tbrWorkType.equals(occDto.getCode())) {
								tbrWorkName = occDto.getCodeName();
							}
						}
				        //被保人信息
				        String bbrName = ExcelUtil.getCellText(hssfRow.getCell(14)).trim();;
			        	String bbrIdType = ExcelUtil.getCellText(hssfRow.getCell(15)).trim();
			        	for (DictDto dto : idTypeList) {
			        		if (bbrIdType.equals(dto.getCodeName())) {
			        			bbrIdType = dto.getCode();
			        		}
			        	}
			        	String bbrIdNo = ExcelUtil.getCellText(hssfRow.getCell(16)).trim();
			        	String bbrBir="";
						if (hssfRow.getCell(17)!=null) {
							bbrBir = DateUtil.format(hssfRow.getCell(17).getDateCellValue(),"yyyy-MM-dd");
						}else{
							bbrBir = IdCardUtil.getBirthdayFromCard(bbrIdNo);
						}
			        	String bbrSex = ExcelUtil.getCellText(hssfRow.getCell(18)).trim();
			        	if ("男".equals(bbrSex)) {
							bbrSex = "1";
						}else if ("女".equals(bbrSex)){
							bbrSex = "2";
						}
			        	if (StrUtil.isEmpty(tbrSex)&&"01".equals(bbrIdType)&&!("01".equals(relWithInsured))) {
				        	bbrSex = IdCardUtil.getIdSexNew(bbrIdNo);
						}
			        	String bbrTel = ExcelUtil.getCellText(hssfRow.getCell(19)).trim();
			        	String bbrNationality = ExcelUtil.getCellText(hssfRow.getCell(20)).trim();
			        	if (StrUtil.isEmpty(bbrNationality)) {
			        		bbrNationality = tbrNationality;
						}
			        	String bbrWorkType = ExcelUtil.getCellText(hssfRow.getCell(21)).trim();
				        String bbrWorkName = "";
						for (DictDto occDto : occupationCodeList) {
							if (bbrWorkType.equals(occDto.getCode())) {
								bbrWorkName = occDto.getCodeName();
							}
						}
				        /*被保人信息赋值       */
				        if ("01".equals(relWithInsured)) {
				        	//如果为本人并且未填写被保人信息则同投保人
				        	if (StrUtil.isEmpty(bbrName)) {
								bbrName = tbrName;
							}
				        	if (StrUtil.isEmpty(bbrIdType)) {
				        		bbrIdType = tbrIdType;
							}
				        	if (StrUtil.isEmpty(bbrIdNo)) {
				        		bbrIdNo = tbrIdNo;
							}
				        	if (StrUtil.isEmpty(bbrTel)) {
				        		bbrTel = tbrTel;
							}
				        	if (StrUtil.isEmpty(bbrNationality)) {
				        		bbrNationality = tbrNationality;
							}
							if (StrUtil.isEmpty(bbrBir)) {
								bbrBir = tbrBir;
							}
							if (StrUtil.isEmpty(bbrSex)){
								bbrSex = tbrSex;
							}
							if (StrUtil.isEmpty(bbrWorkType)){
								bbrWorkType = tbrWorkType;
							}
							if (StrUtil.isEmpty(bbrWorkName)){
								bbrWorkName = tbrWorkName;
							}
						}else{
							for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
								int[] arr = {14,15,16};
								int a =  Arrays.binarySearch(arr, colNum);
								if ((a>=0)&&(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
									map.put("errorMsg", "导入失败：当投保人与被保险人关系非本人时，第" + (rowNum + 1)+ "行数据中第"+(colNum+1)+"列数据为必录项不允许为空，请检查 ");
									return map;
								}
							}
						}

						HashMap<String, Object> listMap = new HashMap<String, Object>();
						listMap.put("cardNo", cardNo);
						listMap.put("cardPw", cardPw);
						listMap.put("effDate", effDate);
						listMap.put("termDate", termDate);
						listMap.put("tbrName", tbrName);
						listMap.put("tbrNature", tbrNature);
						listMap.put("tbrIdType", tbrIdType);
						listMap.put("tbrIdNo", tbrIdNo);
						listMap.put("tbrBir", tbrBir);
						listMap.put("tbrSex", tbrSex);
						listMap.put("tbrTel", tbrTel);
						listMap.put("tbrEmail", tbrEmail);
						listMap.put("tbrNationality", tbrNationality);
						listMap.put("tbrWorkName", tbrWorkName);
						listMap.put("tbrWorkType", tbrWorkType);
						listMap.put("relWithInsured", relWithInsured);
						listMap.put("bbrName", bbrName);
						listMap.put("bbrIdType", bbrIdType);
						listMap.put("bbrIdNo", bbrIdNo);
						listMap.put("bbrBir", bbrBir);
						listMap.put("bbrSex", bbrSex);
						listMap.put("bbrTel", bbrTel);
						listMap.put("bbrNationality", bbrNationality);
						listMap.put("bbrWorkName", bbrWorkName);
						listMap.put("bbrWorkType", bbrWorkType);
						dataList.add(listMap);
					}
				}
				map.put("dataList", dataList);
				return map;
			}
		} else{
			Result.error("上传失败:Excel内容为空 ");
		}
		return null;
	}

	private String checkExcelCard(String cardNo, String cardPw, String cardDefineId, int rowNum) throws Exception {
		CardInfo card = cardInfoDao.getCardInfoByCardNo(cardNo);
		String msg = null;
		if (card == null) {
			msg = "导入失败：第" + (rowNum + 1)+ "行卡号不正确！";
		}
		if (!cardPw.equals(card.getCardPw())) {
			msg = "导入失败：第" + (rowNum + 1)+ "行密码不正确！";
		}
		if (!cardDefineId.equals(card.getCardDefineId())) {
			msg = "卡号：" + cardNo + "跟您选择的卡定义名称不一致！";
		}
		if (!Constants.PROD_TYPE_NLIFE.equals(card.getTypeCode())) {
			msg = "导入失败：激活卡系统目前只支持新意健险激活卡激活！";
		}
		if (!Constants.ACTIR_CARD_ACTIVABLE.equals(card.getStatus())
		        && !Constants.ACTIR_CARD_ACTIVABLE_USED.equals(card.getStatus())) {
			msg = "导入失败：第" + (rowNum + 1)+ "行  此卡不是可激活或已激活状态，不可登录！";
		}
		if (StrUtil.isEmpty(card.getPlanNo())) {
		    msg = "导入失败：第" + (rowNum + 1)+ "行  此卡没有关联方案，请联系管理员！";
		}
		return msg;
	}
	private String chengeTbrNature(String tbrNature){
		if("自然人".equals(tbrNature)){
        	tbrNature="1";
        }else if("企业".equals(tbrNature)){
        	tbrNature="5";
        }else if("军队".equals(tbrNature)){
        	tbrNature="9";
        }else if("外企".equals(tbrNature)){
        	tbrNature="10";
        }else if("村委会".equals(tbrNature)){
        	tbrNature="11";
        }else if("其他社会团体".equals(tbrNature)){
        	tbrNature="7";
        }
		return tbrNature;
	}
	private boolean checkTypeId(String nature,String idType,List<DictDto> idTypeList){
		boolean flag=false;
		if(!"5".equals(nature)){
			for (DictDto dictDto : idTypeList) {
				if("1".equals(nature)&&"NATURE".equals(dictDto.getOtherCode())&&idType.equals(dictDto.getCode())){
					flag=true;
					break;
				}else if((!"1".equals(nature))&&StrUtil.isEmpty(dictDto.getOtherCode())&&idType.equals(dictDto.getCode())){
					flag=true;
					break;
				}
			}
		}
		if("5".equals(nature)&&"26".equals(idType)){flag=true;}
		return flag;
	}
		
	/**
	 * 份数限制,超出字典表配置的数量返回true
	 *
	 * @param facadeAppForm    投保信息
	 * @param facadeAppInsured 被保人信息
	 * @return
	 */
	@Override
	public boolean countCheck(FacadeAppForm facadeAppForm, FacadeAppInsured facadeAppInsured) throws Exception {
		// 获取产品的投保份数
		ActirationCardActivated prodInfoByPlanNo = getProdInfoByPlanNo(facadeAppForm.getInsuPlanNo());
		// 投保份数
		Long unirPp = prodInfoByPlanNo.getUnirPp();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("planNo", facadeAppForm.getInsuPlanNo());
		map.put("effDate", sdf.format(facadeAppForm.getEffDate()));
		map.put("termDate", sdf.format(facadeAppForm.getTermDate()));
		// idType 固定为身份证 01 facadeAppInsured.getIdType()
		map.put("idType", facadeAppInsured.getIdType());
		map.put("idNo", facadeAppInsured.getIdNo());
		map.put("clientName", facadeAppInsured.getClientName());
		// 计算已支付或已承保并且与当前被保人 姓名+证件类型（身份证）+身份证号 相同的单数；
		Integer insuredCount = facadeAppFormDao.queryExistInsuredCount(map);
		return insuredCount >= unirPp;
	}

    @Override
    public boolean countCheckBatch(FacadeAppForm facadeAppForm, FacadeAppInsured facadeAppInsured, String stagNo) throws Exception {
        // 获取产品的投保份数
        ActirationCardActivated prodInfoByPlanNo = getProdInfoByPlanNo(facadeAppForm.getInsuPlanNo());
        // 投保份数
        Long unirPp = prodInfoByPlanNo.getUnirPp();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("planNo", facadeAppForm.getInsuPlanNo());
        map.put("effDate", sdf.format(facadeAppForm.getEffDate()));
        map.put("termDate", sdf.format(facadeAppForm.getTermDate()));
        // idType 固定为身份证 01 facadeAppInsured.getIdType()
        map.put("idType", facadeAppInsured.getIdType());
        map.put("idNo", facadeAppInsured.getIdNo());
        map.put("clientName", facadeAppInsured.getClientName());
        map.put("stagNo", stagNo);
        // 计算已支付或已承保并且与当前被保人 姓名+证件类型（身份证）+身份证号 相同的单数；
        Integer insuredCount = facadeAppFormDao.queryExistInsuredCountBatch(map);
        return insuredCount > unirPp;
    }
}
